import MySQLdb

# 向数据库写入数据--新建联系人
from partial__01 import create_connect


def write_mysql(name, company, tel):
    try:
        with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
            row = cursor.execute(
                'insert into `tb_phonebook`(`name`,`company`,`tel`) '
                'values (%s,%s,%s)',
                (name, company, tel)
            )
            if row == 1:
                print('新增成功')
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        # 失败就回滚
        conn.rollback()
    finally:
        conn.close()


# 查看所有联系人
def view_all():
    try:
        # 第二步：通过连接对象获取游标对象
        with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
            # 第三步：通过游标对象向数据库服务器发出sql语句
            cursor.execute('select `name`,`company`,`tel` from `tb_phonebook`')
            # 第四步：获取查询结果(通过游标抓取数据)
            row_dict = cursor.fetchone()
            while row_dict:
                print(f'联系人姓名：{row_dict["name"]}')
                print(f'公司：{row_dict["company"]}')
                print(f'电话：{row_dict["tel"]}')
                print('-' * 20)
                row_dict = cursor.fetchone()
    except MySQLdb.MySQLError as err:
        print(err)
    finally:
        conn.close()


# 查找联系人
def find_contact(name):
    try:
        # 第二步：通过连接对象获取游标对象
        with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
            # 第三步：通过游标对象向数据库服务器发出sql语句
            cursor.execute('select `name`,`company`,`tel` ,`con_id` from `tb_phonebook` where `name`=%s',
                           (name,))
            # 第四步：获取查询结果(通过游标抓取数据)
            row_dict = cursor.fetchone()
            return row_dict
    except MySQLdb.MySQLError as err:
        print(err)
    finally:
        conn.close()


# 修改联系人信息
def update_contact(name, company, tel, con_id):
    try:
        with conn.cursor() as cursor:
            row = cursor.execute('update`tb_phonebook` set `name`=%s,`company`=%s,`tel`=%s where con_id = %s',
                                 (name, company, tel, con_id))
            if row == 1:
                print('修改联系人成功')
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        # 失败就回滚
        conn.rollback()
    finally:
        conn.close()


# 删除
def delete_con(name):
    con_id = find_contact(name)["con_id"]
    conn = create_connect(database='hrs')
    try:
        # 第二步：通过连接对象获取游标对象
        with conn.cursor() as cursor:
            # 第三步：通过游标对象向数据库服务器发出sql语句
            affected_rows = cursor.execute('delete from  `tb_phonebook` where con_id = %s',
                                           (con_id,)
                                           )
            if affected_rows == 1:
                print('删除成功')
                # 第四步：通过连接对象提交事务
                conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        # 失败就回滚
        conn.rollback()
    finally:
        conn.close()


print('-' * 100)
print('-' * 43 + 'Welcome to the phone book' + '-' * 46)

list1 = ['1.新建联系人', '2.查看所有联系人', '3.查找联系人', '4.修改联系人信息', '5.删除联系人']
for x in list1:
    print(' ' * 47 + x)
conn = create_connect(database='hrs')
op = eval(input('请输入指令数字'))
# 新建
if op == 1:
    name = input('请输入姓名')
    company = input('请输入工作单位')
    tel = input('请输入电话号码')
    write_mysql(name, company, tel)
# 查看所有
elif op == 2:
    view_all()
# 查找
elif op == 3:
    name = input('请输入联系人姓名')
    con = find_contact(name)
    print(f'联系人姓名：{con["name"]}')
    print(f'联系人公司：{con["company"]}')
    print(f'联系人电话：{con["tel"]}')
    print('-' * 20)
elif op == 4:
    name = input('请输入需要修改的联系人姓名')
    con = find_contact(name)
    name = con["name"]
    company = con["company"]
    tel = con["tel"]
    con_id = con["con_id"]
    print(con)
    conn = create_connect(database='hrs')
    up_name = input('请输入姓名')
    up_company = input('请输入该联系人的公司')
    up_tel = input('请输入联系电话')
    update_contact(up_name, up_company, up_tel, con_id)
elif op == 5:
    name = input('请输入需要删除的联系人姓名')
    delete_con(name)